﻿namespace CoreClass
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;

    public class DBCommon
    {
        public string strConnectstring = ConfigurationSettings.AppSettings["ConnectionString"];

        public DataSet ExcuteQueryDataByProc(string p_strProcName, SqlParameter[] cmdParams)
        {
            SqlConnection ConnctionObj = new SqlConnection(this.strConnectstring);
            DataSet dsTemp = new DataSet();
            SqlDataAdapter sqlAdapter = new SqlDataAdapter();
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = p_strProcName;
            sqlCmd.Connection = ConnctionObj;
            for (int i = 0; i < cmdParams.Length; i++)
            {
                sqlCmd.Parameters.Add(cmdParams[i]);
            }
            sqlAdapter.SelectCommand = sqlCmd;
            sqlAdapter.Fill(dsTemp);
            return dsTemp;
        }

        public void ExcuteSql(string strSql)
        {
            SqlConnection sqlCon = new SqlConnection();
            sqlCon.ConnectionString = this.strConnectstring;
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.CommandText = strSql;
            sqlCmd.Connection = sqlCon;
            sqlCon.Open();
            sqlCmd.ExecuteNonQuery();
            sqlCon.Close();
        }

        public int ExcuteSqlCount(string strSql)
        {
            SqlConnection sqlCon = new SqlConnection();
            sqlCon.ConnectionString = this.strConnectstring;
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.CommandText = strSql;
            sqlCmd.Connection = sqlCon;
            sqlCmd.Connection.Open();
            int intCount = sqlCmd.ExecuteNonQuery();
            sqlCmd.Connection.Close();
            sqlCmd.Connection.Dispose();
            sqlCmd.Dispose();
            return intCount;
        }

        public SqlCommand GetCommandBySql()
        {
            SqlConnection conn = new SqlConnection(this.strConnectstring);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            return cmd;
        }

        public SqlCommand GetCommandBySql(string sql)
        {
            SqlCommand cmd = this.GetCommandBySql();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            return cmd;
        }

        public SqlDataAdapter GetDataAdapterBySql(string strSql)
        {
            SqlConnection sqlCon = new SqlConnection();
            sqlCon.ConnectionString = this.strConnectstring;
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.Text;
            sqlCmd.CommandText = strSql;
            sqlCmd.Connection = sqlCon;
            SqlDataAdapter sqlData = new SqlDataAdapter();
            sqlData.SelectCommand = sqlCmd;
            return sqlData;
        }

        public SqlDataReader GetDataReaderBySql(string sql)
        {
            SqlConnection conn = new SqlConnection(this.strConnectstring);
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Connection.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public DataSet GetDataSetBySql(string strSql)
        {
            SqlConnection sqlCon = new SqlConnection(this.strConnectstring);
            SqlCommand sqlCmd = new SqlCommand(strSql, sqlCon);
            sqlCmd.CommandType = CommandType.Text;
            SqlDataAdapter sqlData = new SqlDataAdapter();
            sqlData.SelectCommand = sqlCmd;
            DataSet ds = new DataSet();
            sqlData.Fill(ds);
            return ds;
        }

        public DataTable GetDataTableBySql(string sql)
        {
            SqlConnection conn = new SqlConnection(this.strConnectstring);
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
    }
}

